# 加载所需使用的模块
from datetime import datetime

from vnpy.trader.datafeed import get_datafeed
from vnpy.trader.database import get_database
from vnpy.trader.setting import SETTINGS
import tushare as ts
import pandas as pd
import numpy as np
import pymysql
import time

# 配置数据服务
SETTINGS["datafeed.name"] = "tushare"            # 可以根据自己的需求选择数据服务：rqdata/xt/wind等
SETTINGS["datafeed.username"] = "835983"       # RQData的用户名统一为“license”这个字符串
SETTINGS["datafeed.password"] = "f9070562c743804c9627e56832d30c32a22f2f8c06633357428e6d98"        # 这里需要替换为你购买或者申请试用的RQData数据license

# 配置数据库
SETTINGS["database.name"] = "mysql"              # 可以根据自己的需求选择数据库，这里使用的是TDengine
SETTINGS["database.database"] = "vnpy"
SETTINGS["database.host"] = "127.0.0.1"
SETTINGS["database.port"] = 3306
SETTINGS["database.user"] = "root"
SETTINGS["database.password"] = "12345678"

# 创建对象实例
datafeed = get_datafeed()
pro = ts.pro_api("f9070562c743804c9627e56832d30c32a22f2f8c06633357428e6d98")

database = get_database()
conn = pymysql.connect(
            host="127.0.0.1",
            port=3306,
            user="root",
            password="12345678",
            database="vnpy",
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
loadfundBasicSql = """select * from fund_basic where ts_code not like '%OF%';"""
cursor = conn.cursor()
cursor.execute(loadfundBasicSql)
results = cursor.fetchall()




# 要下载数据的合约代码
# vt_symbols = [
#     "IF2501.CFFEX",
#     "IF2502.CFFEX",
#     "IF2503.CFFEX",

#     "IH2501.CFFEX",
#     "IH2502.CFFEX",
#     "IH2503.CFFEX",

#     "IC2501.CFFEX",
#     "IC2502.CFFEX",
#     "IC2503.CFFEX",

#     "IM2501.CFFEX",
#     "IM2502.CFFEX",
#     "IM2503.CFFEX",datetime
# ]
vt_symbols = [result['ts_code'] for result in results]
#ts_code_list实现replace("SH", "SSE").replace("SZ", "SZSE")
# vt_symbols = [vt_symbol.replace("SH", "SSE").replace("SZ", "SZSE").replace("BJ", "BSE") for vt_symbol in ts_code_lists]


print(vt_symbols)

# 遍历列表执行下载
# for vt_symbol in vt_symbols:
#     # 拆分合约代码和交易所
#     symbol, exchange = extract_vt_symbol(vt_symbol)

#     # 创建历史数据请求对象
#     req: HistoryRequest = HistoryRequest(
#         symbol=symbol,
#         exchange=exchange,
#         start=start,
#         end=end,
#         interval=Interval.DAILY        # 这里下载最常用的1分钟K线
#     )

#     # 从数据服务下载数据
#     bars: list[BarData] = datafeed.query_bar_history(req)

#     # 如果下载成功则保存
#     if bars:
#         database.save_bar_data(bars)
#         print(f"下载数据成功：{vt_symbol}，总数据量：{len(bars)}")
#     # 否则失败则打印信息
#     else:
#         print(f"下载数据失败：{vt_symbol}")
    
#     print("全部下载完成")
#     updateEndTimeSql = """ update dbbaroverview set end = now() where 1=1"""
#     #更新时间为最新更新时间

#     cursor.execute(updateEndTimeSql)
#     print("更新数据库时间成功")
#     conn.commit()
#     conn.close()
pro = ts.pro_api()
for vt_symbol in vt_symbols:
    #延时250毫秒
    time.sleep(0.25)
    # 拆分合约代码和交易所
    # E 表示场内，O 表示场外
    df = pro.fund_daily(ts_code=vt_symbol, start_date='20200101', end_date=datetime.now().strftime('%Y%m%d'))
    df = df.where(pd.notnull(df), None)
    df = df.replace({np.nan: None})
    updateFundSql = """ INSERT INTO `fund_daily` 
    (`ts_code`, `trade_date`, `open`, `high`, `low`, `close`, `pre_close`, `change`, `pct_chg`, `vol`, `amount`
    ) VALUES (
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
    ) """
    data_to_insert = [tuple(item.values()) for item in df.to_dict(orient='records')]
    # print(data_to_insert)

    try:
        with conn.cursor() as cursor:
            cursor.executemany(updateFundSql, data_to_insert)
        conn.commit()
        print(f"成功插入 {len(df)} 条数据")
    except Exception as e:
        conn.rollback()
        print(f"插入失败: {e}")
conn.close()

